package com.whoimi.admin.repository;

import com.whoimi.admin.entity.Log;
import com.whoimi.common.jdbc.ResultListMap;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;
import java.util.Map;

public interface LogRepository extends CrudRepository<Log, Long> {
    @Query(value = "        SELECT " +
            "            COUNT(1) AS count, " +
            "            DATE_FORMAT(create_time,'%Y-%m-%d') AS date " +
            "        FROM " +
            "            wiic_admin.sys_log " +
            "        WHERE " +
            "            create_time BETWEEN :startDate AND :endDate " +
            "          AND is_deleted = 0 " +
            "        GROUP BY " +
            "            DATE_FORMAT(create_time, '%Y-%m-%d') ", resultSetExtractorClass = ResultListMap.class)
    List<Map<String, Object>> getPvCounts(String startDate, String endDate);

    @Query(value = "SELECT " +
            "            COUNT(DISTINCT ip) AS count, " +
            "            DATE_FORMAT(create_time, '%Y-%m-%d') AS date " +
            "        FROM " +
            "            wiic_admin.sys_log " +
            "        WHERE " +
            "            create_time BETWEEN :startDate AND :endDate " +
            "          AND is_deleted = 0 " +
            "        GROUP BY " +
            "            DATE_FORMAT(create_time, '%Y-%m-%d')", resultSetExtractorClass = ResultListMap.class)
    List<Map<String, Object>> getIpCounts(String startDate, String endDate);

    @Query(value = "SELECT\n" +
            "            COUNT(DISTINCT CASE WHEN DATE(create_time) = CURDATE() THEN ip END) AS todayCount,\n" +
            "            COUNT(DISTINCT ip) AS totalCount,\n" +
            "            ROUND(\n" +
            "                CASE\n" +
            "                    WHEN COUNT(DISTINCT CASE WHEN DATE(create_time) = CURDATE() - INTERVAL 1 DAY AND TIME(create_time) <= TIME(NOW()) THEN ip END) = 0 THEN 0\n" +
            "                    ELSE\n" +
            "                        (COUNT(DISTINCT CASE WHEN DATE(create_time) = CURDATE() THEN ip END) -\n" +
            "                         COUNT(DISTINCT CASE WHEN DATE(create_time) = CURDATE() - INTERVAL 1 DAY AND TIME(create_time) <= TIME(NOW()) THEN ip END)) /\n" +
            "                        COUNT(DISTINCT CASE WHEN DATE(create_time) = CURDATE() - INTERVAL 1 DAY AND TIME(create_time) <= TIME(NOW()) THEN ip END)\n" +
            "                    END,\n" +
            "                2) AS growthRate\n" +
            "        FROM\n" +
            "            wiic_admin.sys_log\n" +
            "        WHERE\n" +
            "            is_deleted = 0", resultSetExtractorClass = ResultListMap.class)
    List<Map<String, Object>> getUvStats();

    @Query(value = "SELECT\n" +
            "            COUNT(CASE WHEN DATE(create_time) = CURDATE() THEN 1 END) AS todayCount,\n" +
            "            COUNT(*) AS totalCount,\n" +
            "            ROUND(\n" +
            "                CASE\n" +
            "                    WHEN COUNT(CASE WHEN DATE(create_time) = CURDATE() - INTERVAL 1 DAY AND TIME(create_time) <= TIME(NOW()) THEN 1 END) = 0 THEN 0\n" +
            "                ELSE\n" +
            "                    (COUNT(CASE WHEN DATE(create_time) = CURDATE() THEN 1 END) -\n" +
            "                    COUNT(CASE WHEN DATE(create_time) = CURDATE() - INTERVAL 1 DAY AND TIME(create_time)  <= TIME(NOW()) THEN 1 END)) /\n" +
            "                    COUNT(CASE WHEN DATE(create_time) = CURDATE() - INTERVAL 1 DAY AND TIME(create_time)  <= TIME(NOW()) THEN 1 END)\n" +
            "                END,\n" +
            "            2) AS growthRate\n" +
            "        FROM\n" +
            "            wiic_admin.sys_log\n" +
            "        WHERE\n" +
            "            is_deleted = 0", resultSetExtractorClass = ResultListMap.class)
    List<Map<String, Object>> getPvStats();

}